﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace Entersoft.Data.DotNetDAL.SQL
{
    internal static class ClientHelperParameterCache
    {
        #region private methods, variables
        private static readonly Hashtable paramCache = Hashtable.Synchronized(new Hashtable());

        /// <summary>
        /// Resolve at run time the appropriate set of SqlParameters for a stored procedure
        /// </summary>
        /// <param name="connection">A valid SqlConnection object</param>
        /// <param name="spName">The name of the stored procedure</param>
        /// <param name="includeReturnValueParameter">Whether or not to include their return value parameter></param>
        /// <returns>A list object with SqlParameters</returns>
        private static IList<SqlParameter> DiscoverSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
        {
            if ((connection == null))
                throw new ArgumentNullException("connection");

            if (string.IsNullOrEmpty(spName))
                throw new ArgumentNullException("spName");

            var cmd = new SqlCommand(spName, connection) { CommandType = CommandType.StoredProcedure };

            connection.Open();

            SqlCommandBuilder.DeriveParameters(cmd);

            connection.Close();

            if (!includeReturnValueParameter)
                cmd.Parameters.RemoveAt(0);

            var discoveredParameters = new SqlParameter[cmd.Parameters.Count];

            cmd.Parameters.CopyTo(discoveredParameters, 0);

            // Init the parameters with a DBNull value

            foreach (var discoveredParameter_loopVariable in discoveredParameters)
            {
                var discoveredParameter = discoveredParameter_loopVariable;
                discoveredParameter.Value = DBNull.Value;
            }

            return discoveredParameters;
        }

        /// <summary>
        /// Deep copy of cached SqlParameter array
        /// </summary>
        /// <param name="originalParameters">A list of SqlParameter original</param>
        /// <returns>A list of cloned parameters</returns>
        private static IList<SqlParameter> CloneParameters(IList<SqlParameter> originalParameters)
        {
            var j = originalParameters.Count - 1;

            var clonedParameters = new SqlParameter[j + 1];

            for (var i = 0; i <= j; i++)
                clonedParameters[i] = (SqlParameter)((ICloneable)originalParameters[i]).Clone();

            return clonedParameters;
        }
        #endregion

        #region caching functions
        /// <summary>
        /// Add parameter array to the cache
        /// Parameters
        /// </summary>
        /// <param name="connectionString">a valid connection string for a SqlConnection</param> 
        /// <param name="commandText">the stored procedure name or T-Sql command</param> 
        /// <param name="commandParameters">an array of SqlParamters to be cached</param> 
        public static void CacheParameterSet(string connectionString, string commandText, IList<SqlParameter> commandParameters)
        {
            if (string.IsNullOrEmpty(connectionString))
                throw new ArgumentNullException("connectionString");

            if (string.IsNullOrEmpty(commandText))
                throw new ArgumentNullException("commandText");

            var hashKey = connectionString + ":" + commandText;

            paramCache[hashKey] = commandParameters;
        }

        /// <summary>
        /// retrieve a parameter array from the cache
        /// Parameters:
        /// </summary>
        /// <param name="connectionString">a valid connection string for a SqlConnection</param> 
        /// <param name="commandText">the stored procedure name or T-Sql command</param> 
        /// <returns>An array of SqlParamters</returns> 
        public static IList<SqlParameter> GetCachedParameterSet(string connectionString, string commandText)
        {
            if (string.IsNullOrEmpty(connectionString))
                throw new ArgumentNullException("connectionString");

            if (string.IsNullOrEmpty(commandText))
                throw new ArgumentNullException("commandText");

            var hashKey = connectionString + ":" + commandText;

            var cachedParameters = (IList<SqlParameter>)paramCache[hashKey];

            return cachedParameters == null ? null : CloneParameters(cachedParameters);
        }

        #endregion

        #region Parameter Discovery Functions
        /// <summary>
        /// Retrieves the set of SqlParameters appropriate for the stored procedure.
        /// This method will query the database for this information, and then store it in a cache for future requests.
        /// </summary>
        /// <param name="connectionString">a valid connection string for a SqlConnection</param> 
        /// <param name="spName">the name of the stored procedure</param> 
        /// <returns>An array of SqlParameters</returns>
        public static IList<SqlParameter> GetSpParameterSet(string connectionString, string spName)
        {
            return GetSpParameterSet(connectionString, spName, false);
        }

        /// <summary>
        /// Retrieves the set of SqlParameters appropriate for the stored procedure.
        /// This method will query the database for this information, and then store it in a cache for future requests.
        /// </summary>
        /// <param name="connectionString">a valid connection string for a SqlConnection</param>
        /// <param name="spName">the name of the stored procedure</param> 
        /// <param name="includeReturnValueParameter">a bool value indicating whether the return value parameter should be included in the results</param> 
        /// <returns>An array of SqlParameters</returns> 
        public static IList<SqlParameter> GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
        {
            IList<SqlParameter> functionReturnValue;

            if (string.IsNullOrEmpty(connectionString))
                throw new ArgumentNullException("connectionString");

            SqlConnection connection = null;

            try
            {
                connection = new SqlConnection(connectionString);
                functionReturnValue = GetSpParameterSetInternal(connection, spName, includeReturnValueParameter);
            }
            finally
            {
                if ((connection != null))
                    connection.Dispose();
            }

            return functionReturnValue;
        }

        /// <summary>
        /// Retrieves the set of SqlParameters appropriate for the stored procedure.
        /// This method will query the database for this information, and then store it in a cache for future requests.
        /// </summary>
        /// <param name="connection">a valid SqlConnection object</param>
        /// <param name="spName">the name of the stored procedure</param> 
        /// <returns>A list of SqlParameters</returns> 
        public static IList<SqlParameter> GetSpParameterSet(SqlConnection connection, string spName)
        {
            return GetSpParameterSet(connection, spName, false);
        }

        /// <summary>
        /// Retrieves the set of SqlParameters appropriate for the stored procedure.
        /// This method will query the database for this information, and then store it in a cache for future requests.
        /// </summary>
        /// <param name="connection">a valid SqlConnection object</param>
        /// <param name="spName">the name of the stored procedure</param> 
        /// <param name="includeReturnValueParameter">a bool value indicating whether the return value parameter should be included in the results</param> 
        /// <returns>An array of SqlParameters</returns> 
        public static IList<SqlParameter> GetSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
        {
            IList<SqlParameter> functionReturnValue;

            if ((connection == null))
                throw new ArgumentNullException("connection");

            SqlConnection clonedConnection = null;

            try
            {
                clonedConnection = (SqlConnection)(((ICloneable)connection).Clone());
                functionReturnValue = GetSpParameterSetInternal(clonedConnection, spName, includeReturnValueParameter);
            }
            finally
            {
                if ((clonedConnection != null))
                    clonedConnection.Dispose();
            }

            return functionReturnValue;
        }

        /// <summary>
        /// Retrieves the set of SqlParameters appropriate for the stored procedure.
        /// This method will query the database for this information, and then store it in a cache for future requests.
        /// </summary>
        /// <param name="connection">a valid SqlConnection object</param>
        /// <param name="spName">the name of the stored procedure</param> 
        /// <param name="includeReturnValueParameter">a bool value indicating whether the return value parameter should be included in the results</param> 
        /// <returns>An array of SqlParameters</returns> 
        private static IList<SqlParameter> GetSpParameterSetInternal(SqlConnection connection, string spName, bool includeReturnValueParameter)
        {

            if ((connection == null))
                throw new ArgumentNullException("connection");

            if (string.IsNullOrEmpty(spName))
                throw new ArgumentNullException("spName");

            string hashKey = connection.ConnectionString + ":" + spName + (includeReturnValueParameter ? ":include ReturnValue Parameter" : "");

            var cachedParameters = (IList<SqlParameter>)paramCache[hashKey];

            if ((cachedParameters == null))
            {
                var spParameters = DiscoverSpParameterSet(connection, spName, includeReturnValueParameter);
                paramCache[hashKey] = spParameters;
                cachedParameters = spParameters;
            }

            return CloneParameters(cachedParameters);

        }
        #endregion
    }

}
